package il.ac.tau.dbcourse.db.reports;

import il.ac.tau.dbcourse.db.Album;
import il.ac.tau.dbcourse.db.DBException;
import il.ac.tau.dbcourse.db.Track;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class ExistingTrack {

	public static class TrackDetails {
		private Track track;
		String filePath = "";

		protected TrackDetails(Track track, String filePath) {
			this.track = track;
			this.filePath = filePath;
		}

		public Track getTrack() {
			return track;
		}

		public String getFilePath() {
			return filePath;
		}
	}

	public static List<TrackDetails> getExistingTracks(Connection conn) throws DBException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			List<TrackDetails> results = new ArrayList<TrackDetails>();
			stmt = conn.prepareStatement("SELECT Track_Id,Path "
					+ "FROM User_Track \"UT\" ,Track \"T\" "
					+ "WHERE Track_Id is not null " + "AND UT.Track_Id=T.ID");
			rs = stmt.executeQuery();

			while (rs.next()) {
				Track track = new Track(conn, rs.getLong(1));
				String filePath = rs.getString(2);
				TrackDetails details = new TrackDetails(track, filePath);
				results.add(details);
			}

			return results;

		} catch (SQLException e) {
			throw new DBException("Can't get tracks - " + e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
		}

	}

	public static List<TrackDetails> getExistingTracks(Connection conn,Album album)
			throws DBException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			List<TrackDetails> results = new ArrayList<TrackDetails>();
			Set<Integer> trackNumbers=new HashSet<Integer>();
			stmt = conn.prepareStatement("SELECT Track_Id,Path,T.Track_Number "
					+ "FROM User_Track \"UT\" ,Track \"T\" "
					+ "WHERE Track_Id is not null " + "AND UT.Track_Id=T.ID "
					+ "AND T.Album_Id=? ORDER BY T.Track_Number");
			stmt.setLong(1, album.getId());
			rs = stmt.executeQuery();

			while (rs.next()) {
				if (trackNumbers.contains(rs.getInt(3))) continue;
				trackNumbers.add(rs.getInt(3));
				Track track = new Track(conn, rs.getLong(1));
				String filePath = rs.getString(2);
				TrackDetails details = new TrackDetails(track, filePath);
				results.add(details);
			}

			return results;

		} catch (SQLException e) {
			throw new DBException("Can't get tracks - " + e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
			
		}



	}
}
